﻿using DBUtil.Attributes;
using NUnit.Framework;
using Shouldly;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test.MySql.Selects.SubSelects
{
    [TestFixture]
    internal class SelectBuilder2 : TestBase
    {
        #region model
        [Table("test")]
        public class AEntity
        {
            [Column("a_id")]
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            public int Id { get; set; }
            [Column("a_name")]
            public string Name { get; set; }
            [Column("a_addr")]
            public string Addr { get; set; }
            public int Age { get; set; }
        }
        [Table("test2")]
        public class BEntity
        {
            [Column("b_id")]
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            public int Id { get; set; }
            [Column("b_name")]
            public string Name { get; set; }
            [Column("b_addr")]
            public string Addr { get; set; }
            public int Age { get; set; }
        }
        #endregion

        [Test]
        public void FirstOrDefault()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).FirstOrDefault((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id = (select t.a_id
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10
                limit 1);
                """);
        }

        [Test]
        public void ToList()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).ToList((a, b) => a.Id).Contains(i.t2.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id in (select t.a_id
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10);
                """);
        }

        [Test]
        public void Max()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).Max((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id = ((select max(t.a_id)
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10));
                """);
        }

        [Test]
        public void Min()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).Min((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id = ((select min(t.a_id)
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10));
                """);
        }

        [Test]
        public void Sum()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).Sum((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id = ((select sum(t.a_id)
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10));
                """);
        }

        [Test]
        public void Avg()
        {
            var sql = db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id)
                .Where(i => i.t2.Id == db.Select<AEntity>().LeftJoin<BEntity>((a, b) => a.Id == b.Id).Where(i => i.t.Id == 10).Avg((a, b) => a.Id))
                .ToSqlList((a, b) => new { a, b });
            sql.ShouldBe("""
                select t.a_id `a.Id`,t.a_name `a.Name`,t.a_addr `a.Addr`,t.`Age` `a.Age`,t2.b_id `b.Id`,t2.b_name `b.Name`,t2.b_addr `b.Addr`,t2.`Age` `b.Age`
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t2.b_id = ((select avg(t.a_id)
                from test t
                    left join test2 t2 on t.a_id = t2.b_id
                where t.a_id = 10));
                """);
        }
    }
}
